package vn.aptech.leaveManagementSystem.dao;

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import vn.aptech.leaveManagementSystem.business.dto.ConfigDatabaseDTO;

//import vn.aptech.leaveManagementSystem.business.dto.UserLoginDTO;

public class DBManager {
	protected Connection conn;
	
	public DBManager() {
		setDriver();
	}	
	
	protected void  setDriver()
	{
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {			
			e.printStackTrace();
		}
	}
	
	public Connection getConn() throws SQLException
    {     
        String port = ConfigDatabaseDTO.getPort();
        String serverUrl = ConfigDatabaseDTO.getServerName();
        String database = ConfigDatabaseDTO.getDatabaseName();
        String username = ConfigDatabaseDTO.getLogin();
        String password=ConfigDatabaseDTO.getPassword();
        conn = DriverManager.getConnection("jdbc:sqlserver://"+serverUrl+":"+port+";databaseName="+database,username,password);    
        return conn;
    }
	
	public ResultSet runQuery(String sql) throws SQLException
	{
		getConn();
		return conn.createStatement().executeQuery(sql);
	}
	
	

public boolean loginAdmin(String user, String pwd) {
	// TODO Auto-generated method stub
	boolean result = false;        
    try 
    {
        String sql = "select * from [Employee] where UserName = ? and Password = ? and PositionID = 1";
        PreparedStatement ps = this.getConn().prepareStatement(sql);
        ps.setString(1,user);
        ps.setString(2,pwd);
        ResultSet rs = ps.executeQuery();
        if(rs.next())
        {
            result = true;
        }  
        rs.close();
        
    } 
    catch (Exception ex) 
    {
        ex.printStackTrace();
    }
    return result;
}

	public boolean loginDirector(String user, String pwd) {
		// TODO Auto-generated method stub
		boolean result = false;        
	    try 
	    {
	        String sql = "select * from [Employee] where UserName = ? and Password = ? and PositionID = 2";
	        PreparedStatement ps = this.getConn().prepareStatement(sql);
	        ps.setString(1,user);
	        ps.setString(2,pwd);
	        ResultSet rs = ps.executeQuery();
	        if(rs.next())
	        {
	            result = true;
	        }  
	        rs.close();
	        
	    } 
	    catch (Exception ex) 
	    {
	        ex.printStackTrace();
	    }
	    return result;
	}
	
	public boolean loginManager(String user, String pwd) {
		// TODO Auto-generated method stub
		boolean result = false;
		 
		try {
				String sql = "select * from Employee where UserName = ? and Password = ? and PositionID = 3";
				PreparedStatement ps = this.getConn().prepareStatement(sql);
				ps.setString(1, user);
				ps.setString(2, pwd);
				ResultSet rs = ps.executeQuery();
				if (rs.next()) {
					result = true;
				}
				rs.close();
				
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return result;
	}
	
	public boolean loginEmployee(String user, String pwd) {
		// TODO Auto-generated method stub
	
		// TODO Auto-generated method stub
		boolean result = false;
		 
		try {
				String sql = "select * from Employee where UserName = ? and Password = ? and PositionID = 4";
				PreparedStatement ps = this.getConn().prepareStatement(sql);
				ps.setString(1, user);
				ps.setString(2, pwd);
				ResultSet rs = ps.executeQuery();
				if (rs.next()) {
					result = true;
				}
				rs.close();
				
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return result;
	
	}
	
}
